#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
快速检查答题数据和排行榜
"""

import pymysql

DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'quiz_system',
    'charset': 'utf8mb4'
}

def check_data():
    print("=" * 60)
    print("📊 检查答题数据和排行榜")
    print("=" * 60)
    
    try:
        conn = pymysql.connect(**DB_CONFIG)
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # 1. 检查答题记录总数
        print("\n1️⃣  答题记录统计:")
        cursor.execute("SELECT COUNT(*) as count FROM quiz_results")
        total = cursor.fetchone()['count']
        print(f"   总答题记录数: {total}")
        
        if total == 0:
            print("   ❌ 没有任何答题记录！")
            print("   请先完成至少一次答题。")
            conn.close()
            return
        
        # 2. 检查有答题记录的用户
        print("\n2️⃣  用户答题情况:")
        cursor.execute("""
            SELECT COUNT(DISTINCT user_id) as count 
            FROM quiz_results 
            WHERE user_id IS NOT NULL
        """)
        users_count = cursor.fetchone()['count']
        print(f"   有答题记录的用户数: {users_count}")
        
        # 3. 显示前5名用户
        print("\n3️⃣  用户答题详情（前5名）:")
        cursor.execute("""
            SELECT 
                u.username,
                COUNT(q.id) as quiz_count,
                SUM(q.score) as total_score,
                ROUND(AVG(q.score), 1) as avg_score,
                ROUND(SUM(q.correct_count) * 100.0 / NULLIF(SUM(q.correct_count + q.wrong_count), 0), 1) as accuracy
            FROM users u
            LEFT JOIN quiz_results q ON u.id = q.user_id
            GROUP BY u.id, u.username
            ORDER BY quiz_count DESC, total_score DESC
            LIMIT 5
        """)
        
        users = cursor.fetchall()
        for i, user in enumerate(users, 1):
            print(f"\n   {i}. 用户：{user['username']}")
            print(f"      答题次数: {user['quiz_count'] or 0}次")
            print(f"      总得分: {user['total_score'] or 0}分")
            print(f"      平均分: {user['avg_score'] or 0}分")
            print(f"      正确率: {user['accuracy'] or 0}%")
        
        # 4. 测试排行榜查询
        print("\n4️⃣  测试排行榜查询:")
        
        # 总分榜
        cursor.execute("""
            SELECT u.username, SUM(q.score) as total_score, COUNT(q.id) as total_quizzes
            FROM users u
            INNER JOIN quiz_results q ON u.id = q.user_id
            GROUP BY u.id, u.username
            ORDER BY total_score DESC
            LIMIT 3
        """)
        
        leaderboard = cursor.fetchall()
        if leaderboard:
            print("\n   ✅ 总分榜 TOP3:")
            for i, user in enumerate(leaderboard, 1):
                print(f"      {i}. {user['username']}: {user['total_score']}分 ({user['total_quizzes']}次)")
        else:
            print("   ❌ 排行榜查询为空")
        
        # 5. 检查最近的答题记录
        print("\n5️⃣  最近5条答题记录:")
        cursor.execute("""
            SELECT 
                u.username,
                q.score,
                q.correct_count,
                q.wrong_count,
                q.category_name,
                q.created_at
            FROM quiz_results q
            INNER JOIN users u ON q.user_id = u.id
            ORDER BY q.created_at DESC
            LIMIT 5
        """)
        
        recent = cursor.fetchall()
        for record in recent:
            print(f"\n   📝 {record['username']}")
            print(f"      分类: {record['category_name']}")
            print(f"      得分: {record['score']}分")
            print(f"      答对/答错: {record['correct_count']}/{record['wrong_count']}")
            print(f"      时间: {record['created_at']}")
        
        conn.close()
        
        print("\n" + "=" * 60)
        print("✅ 检查完成！")
        print("=" * 60)
        
        if total > 0 and users_count > 0:
            print("\n💡 数据正常！")
            print("   如果排行榜还是没显示，请：")
            print("   1. 重启Flask服务器（Ctrl+C，然后 python hello.py）")
            print("   2. 清除浏览器缓存（Ctrl+Shift+Delete）")
            print("   3. 刷新排行榜页面（Ctrl+F5）")
        else:
            print("\n⚠️  数据异常！")
            print("   请先完成至少一次答题。")
        
    except Exception as e:
        print(f"\n❌ 错误: {e}")
        import traceback
        traceback.print_exc()

if __name__ == '__main__':
    check_data()

